package nl.ruudshuis.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DBmanager {

	private static DBmanager uniqueInstance = null;

	private Connection con = null;
	private final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	private String dbname;

	private DBmanager(String dbname) {
		this.dbname = dbname+";create=true";
		
		if (!dbExists(this.dbname)) {
			System.err.println("de database bestaat niet....");
		}
			Statement stmt;
			try {
				stmt = con.createStatement();
				stmt.execute("SELECT * FROM VEILING");
			} catch (SQLException e) {
				
				try {
					stmt = con.createStatement();
					System.out.println("database gemaakt");
					stmt.execute("CREATE TABLE VEILING (veilingId INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), verkoper INTEGER, imgPath VARCHAR(100), titel VARCHAR(50), omschrijving VARCHAR(" +Integer.MAX_VALUE+"), prijs DOUBLE, eindtijd BIGINT, CONSTRAINT veiling_pk PRIMARY KEY (veilingId))");
					stmt.execute("CREATE TABLE GEBRUIKER (userId INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), email VARCHAR(50), pass VARCHAR(50), woonplaats VARCHAR(50), telnr VARCHAR(50), CONSTRAINT user_pk PRIMARY KEY (email))");
					stmt.execute("CREATE TABLE BIEDING (veilingId INTEGER, userId INTEGER, prijs DOUBLE, PRIMARY KEY (veilingId, userId, prijs))");
					stmt.close();
					con.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				
			}
	}

	public static synchronized DBmanager getInstance(String dbname) {
		if (uniqueInstance == null) {
			uniqueInstance = new DBmanager(dbname);
		}
		return uniqueInstance;
	}

	private boolean dbExists(String dataSource) {
		boolean exists = false;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(dataSource);
			if(con != null){
				System.out.println("Connection established");
			}

			exists = true;
		} catch (Exception e) {
			; // Do nothing, as DB does not (yet) exist
		}
		return (exists);
	}

	public void close() {
		try {
			con = DriverManager.getConnection(dbname + ";shutdown=true");
		} catch (SQLException se) {
			; // Do Nothing. System has shut down.
			con = null;
		}
	}

	public Connection getConnection() {
		return con;
	}
}